﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ag_showdup_collect]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp_ag_showdup_collect]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
	usp_ag_showdup_collect: Collect duplicate references and store them into the #Duplicates temporary table (which needs to be created by the caller)

	Copyright 2010 by Alberto Gemin
	agemin@hotmail.com
	Version 1.1
	30 Jul 2011

	This procedure is usually called by usp_ag_showdup, but it can be executed directly as long as the caller creates the #Duplicates
	temporary table beforehand with a statement similar to the following:

		CREATE TABLE #Duplicates (
			Id INTEGER IDENTITY(1,1) NOT NULL,	-- Primary Key
			RuleId UNIQUEIDENTIFIER,			-- Rule Id (FK to DuplicateRule)
			BaseEntityTypeCode INTEGER,			-- ObjectTypeCode of the base entity
			BaseObjectId UNIQUEIDENTIFIER,		-- Base Entity Id (FK to the base entity table)
			MatchingEntityTypeCode INTEGER,		-- ObjectTypeCode of the matching entity
			MatchingObjectId UNIQUEIDENTIFIER,	-- Matching Entity Id (FK to the matching entity table)
			Matchcode NVARCHAR(450),			-- Matchcode that was used for duplicate detection
		)

	In the above, "base entity" refers to the entity for which duplicates are detected/processed, while "matching entity" is the duplicate.
	Usually this is the same entity as the base entity, but in CRM 2011 one can deduplicate across different entities, for example contacts
	againts accounts.

	Note that because this procedure accesses low level objects, it must be executed by a system administrator. A normal CRM user will not be able
	to execute it.

*/
CREATE PROCEDURE [dbo].[usp_ag_showdup_collect] (
	@BaseEntityName NVARCHAR(160))				-- Base Entity for Duplication Detection (i.e. 'Contact', 'Account', etc.)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @BaseDuplicateCursor				CURSOR
	DECLARE @BaseDuplicateEscapedMatchcode		NVARCHAR(900)
	DECLARE @BaseDuplicateMatchcode				NVARCHAR(450)
	DECLARE @BaseDuplicateObjectId				UNIQUEIDENTIFIER
	DECLARE @BaseEntityId						UNIQUEIDENTIFIER
	DECLARE @BaseEntityTableKey					NVARCHAR(50)
	DECLARE @BaseEntityTypeCode					INTEGER
	DECLARE @RuleBaseEntityMatchcodeTable		NVARCHAR(50)
	DECLARE @RuleCursor							CURSOR
	DECLARE @RuleDuplicateRuleId				UNIQUEIDENTIFIER
	DECLARE @RuleMatchingEntityMatchcodeTable	NVARCHAR(50)
	DECLARE @RuleMatchingEntityTypeCode			INTEGER
	DECLARE @SqlQuery							NVARCHAR(MAX)

	-- Get some property of the base table
	SELECT  @BaseEntityId = ev.EntityId, @BaseEntityTypeCode = ev.ObjectTypeCode FROM EntityView AS ev WHERE ev.Name = @BaseEntityName
	SELECT  @BaseEntityTableKey = av.PhysicalName FROM AttributeView av WHERE av.EntityId = @BaseEntityId AND av.IsPKAttribute = 1

--	Debug Print
--	PRINT '@BaseEntityId:       ' + ISNULL(CAST(@BaseEntityId AS VARCHAR(40)), '<null>')
--	PRINT '@BaseEntityName:     ' + ISNULL(@BaseEntityName, '<null>')
--	PRINT '@BaseEntityTableKey: ' + ISNULL(@BaseEntityTableKey, '<null>')
--	PRINT '@BaseEntityTypeCode: ' + ISNULL(CAST(@BaseEntityTypeCode AS VARCHAR(20)), '<null>')

	-- This temporary table collects all matchcodes from multiple rules
	CREATE TABLE #BaseDuplicates(ObjectId UNIQUEIDENTIFIER, Matchcode NVARCHAR(450))

	-- Go through all active rules related to the base entity
	SET @RuleCursor = CURSOR for
		SELECT
			BaseEntityMatchcodeTable,
			DuplicateRuleId,
			MatchingEntityMatchcodeTable,
			MatchingEntityTypeCode
		FROM
			DuplicateRule
		WHERE
			StateCode = 1
		AND BaseEntityTypeCode = @BaseEntityTypeCode

	OPEN @RuleCursor
	FETCH NEXT FROM @RuleCursor INTO
		@RuleBaseEntityMatchcodeTable,
		@RuleDuplicateRuleId,
		@RuleMatchingEntityMatchcodeTable,
		@RuleMatchingEntityTypeCode

	WHILE @@FETCH_STATUS = 0
	BEGIN		-- Rule looop

		SET @SqlQuery = 'INSERT INTO #BaseDuplicates(ObjectId, Matchcode)'
		SET @SqlQuery = @SqlQuery + ' SELECT DISTINCT mc.ObjectId,'
		SET @SqlQuery = @SqlQuery + ' mc.Matchcode'
		SET @SqlQuery = @SqlQuery + ' FROM'
		SET @SqlQuery = @SqlQuery + ' ' + @RuleBaseEntityMatchcodeTable + ' AS mc'
		SET @SqlQuery = @SqlQuery + ' INNER JOIN [' + @BaseEntityName + '] AS e ON e.[' + @BaseEntityTableKey + '] = mc.ObjectId'
		SET @SqlQuery = @SqlQuery + ' INNER JOIN DuplicateRecord AS dr ON dr.BaseRecordId = mc.ObjectId'

--	Debug Print
--		PRINT '@RuleBaseEntityMatchcodeTable:     ' + ISNULL(@RuleBaseEntityMatchcodeTable, '<null>')
--		PRINT '@RuleDuplicateRuleId:              ' + ISNULL(CAST(@RuleDuplicateRuleId AS VARCHAR(40)), '<null>')
--		PRINT '@RuleMatchingEntityMatchcodeTable: ' + ISNULL(@RuleMatchingEntityMatchcodeTable, '<null>')
--		PRINT '@RuleMatchingEntityTypeCode:       ' + ISNULL(CAST(@RuleMatchingEntityTypeCode AS VARCHAR(20)), '<null>')
--		PRINT '@SqlQuery:                         ' + ISNULL(@SqlQuery, '<null>')

		EXECUTE(@SqlQuery)

		-- Go through all the matchcodes just collected
		SET @BaseDuplicateCursor = CURSOR for
			SELECT
				ObjectId,
				Matchcode
			FROM
				#BaseDuplicates

		OPEN @BaseDuplicateCursor
		FETCH NEXT FROM @BaseDuplicateCursor INTO
			@BaseDuplicateObjectId,
			@BaseDuplicateMatchcode

		WHILE @@FETCH_STATUS = 0
		BEGIN		-- Base Matchcode looop

			SET @BaseDuplicateEscapedMatchcode = REPLACE(@BaseDuplicateMatchcode, '''', '''''')

			SET @SqlQuery = 'INSERT INTO #Duplicates(RuleId, BaseEntityTypeCode, BaseObjectId, MatchingEntityTypeCode, MatchingObjectId, Matchcode)'
			SET @SqlQuery = @SqlQuery + ' SELECT ''' + CAST(@RuleDuplicateRuleId AS VARCHAR(40)) + ''','
			SET @SqlQuery = @SqlQuery + ' ' + CAST(@BaseEntityTypeCode AS VARCHAR(20)) + ','
			SET @SqlQuery = @SqlQuery + ' ''' + CAST(@BaseDuplicateObjectId AS VARCHAR(40)) + ''','
			SET @SqlQuery = @SqlQuery + ' ' + CAST(@RuleMatchingEntityTypeCode AS VARCHAR(20)) + ','
			SET @SqlQuery = @SqlQuery + ' ObjectId,'
			SET @SqlQuery = @SqlQuery + ' Matchcode'
			SET @SqlQuery = @SqlQuery + ' FROM ' + @RuleMatchingEntityMatchcodeTable
			SET @SqlQuery = @SqlQuery + ' WHERE ObjectId <> ''' + CAST(@BaseDuplicateObjectId AS VARCHAR(40)) + ''''
			SET @SqlQuery = @SqlQuery + ' AND Matchcode = ''' + @BaseDuplicateEscapedMatchcode + ''''
			SET @SqlQuery = @SqlQuery + ' AND Matchcode IS NOT NULL'
			SET @SqlQuery = @SqlQuery + ' AND CAST(Matchcode As varbinary(900)) = CAST(N''' + @BaseDuplicateEscapedMatchcode + ''' As varbinary(900))'

			-- Insert into #Duplicates
			EXECUTE(@SqlQuery)

			FETCH NEXT FROM @BaseDuplicateCursor INTO
				@BaseDuplicateObjectId,
				@BaseDuplicateMatchcode
		END			-- Base Matchcode looop
		CLOSE @BaseDuplicateCursor
		DEALLOCATE @BaseDuplicateCursor

		TRUNCATE TABLE #BaseDuplicates

		FETCH NEXT FROM @RuleCursor INTO
			@RuleBaseEntityMatchcodeTable,
			@RuleDuplicateRuleId,
			@RuleMatchingEntityMatchcodeTable,
			@RuleMatchingEntityTypeCode

	END			-- Rule looop
	CLOSE @RuleCursor
	DEALLOCATE @RuleCursor

END
GO
